'use strict';

/** @type Egg.EggPlugin */

const moment = require('moment')
// 分页器
const getPageNum = (pageNo,pageSize)=>(pageNo - 1) * pageSize
// 毫秒级时间
const day = _=> 24 * 60 * 60 * 1000
const month = _=> day() * 30
const now = _=> (new Date()).getTime()


module.exports = {
  // 查询商家列表
  businessList:(data)=>`SELECT * from business WHERE title LIKE '%${data.title}%' AND isGrounding=1 AND endTime>NOW() ORDER BY ord DESC,id DESC`,

  // 商家列表详情
  businessDetail:(data)=>`SELECT * from business WHERE id=${data.id} AND isGrounding=1 AND endTime>NOW() ORDER BY id DESC`,

  // 活动列表
  activeList:(data)=>`SELECT * from active WHERE uSchoolId=${data.uSchoolId} and title LIKE '%${data.title}%' AND isGrounding=1 AND type=1 ORDER BY ord DESC,id DESC`,

  // 活动列表详情
  activeDetail:(data)=>`SELECT * from active WHERE id=${data.id} ORDER BY id DESC`,

  // 咨讯列表
  newsList:(data)=>`SELECT * from active WHERE uSchoolId=${data.uSchoolId} and title LIKE '%${data.title}%' AND isGrounding=1 AND type=2 ORDER BY ord DESC,id DESC`,

  // 咨讯列表详情
  newsDetail:(data)=>`SELECT * from active WHERE id=${data.id} ORDER BY id DESC`,

  // 高校名称模糊查询
  getSchoolName:(data)=>`SELECT id,name from school WHERE name like '%${data.name}%' ORDER BY id DESC`,

  // 查询券码是否重复
  getNum:(uBusinessId)=>`SELECT  count(*)  from  uOrder WHERE status>2 and uBusinessId=${uBusinessId}`,

  // 查询商家列表
  uBusinessList:(data)=>`SELECT  *  from  uBusiness WHERE uSchoolId=${data.uSchoolId} and topNum=${data.topNum} and endTime>now()`,

  // 多条件查询商家家信息
  uFilterBusiness:(data)=>{
    const { uSchoolId, uType, filter } = data
    let sqlStr = ''
    const colums= 'id, src,title,endTime,price,priceOld'
    if (filter === 'num') {
        sqlStr = `SELECT * from  uBusiness WHERE uSchoolId=${uSchoolId} and uType=${uType} and isGrounding=1 AND endTime>NOW() ORDER BY num DESC`
    } else if (filter === 'time') {
        sqlStr = `SELECT * from  uBusiness WHERE uSchoolId=${uSchoolId} and uType=${uType} and isGrounding=1 AND endTime>NOW() ORDER BY time DESC`
    } else if (filter === 'price') {
        sqlStr = `SELECT * from  uBusiness WHERE uSchoolId=${uSchoolId} and uType=${uType} and isGrounding=1 AND endTime>NOW() ORDER BY price`
    } else if (filter === 'priceDesc') {
        sqlStr = `SELECT * from  uBusiness WHERE uSchoolId=${uSchoolId} and uType=${uType} and isGrounding=1 AND endTime>NOW() ORDER BY price DESC`
    }
   
    return sqlStr
  },

  // 获取校园管理者分页列表
  uSchoolManageList:(data)=>`SELECT * from loginManage where type=${data.type} and if(${data.schoolId}>0,schoolId=${data.schoolId},1=1) ORDER BY manageId DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`,
  uSchoolManageTotal:(data)=>`SELECT count(*) from loginManage where type=${data.type} and if(${data.schoolId}>0,schoolId=${data.schoolId},1=1)`,

  // 获取校园入驻分页列表
  uSchoolList:(data)=>`SELECT * from uSchool  ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`,
  uSchoolTotal:()=>`SELECT count(*) from uSchool`,

  // 商铺分页列表
  uBusinessManageList:(data)=>`SELECT * from uBusiness where manageId=${data.manageId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1)  ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`,
  uBusinessManageTotal:(data)=>`SELECT count(*) from uBusiness where manageId=${data.manageId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1)`,

  // 商铺套餐列表
  uTicketList:(data)=>`SELECT * from uTicket where uBusinessId=${data.uBusinessId} ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`,
  uTicketTotal:(data)=>`SELECT count(*) from uTicket where uBusinessId=${data.uBusinessId}`,
  // 查询商铺下套餐数量
  uTicketNum:(data)=>`SELECT count(*) from uTicket where uBusinessId = ${data.uBusinessId}`,

  // 商家订单列表   
  uOrderManageList:(data)=>{
    let sqlStr = ''
    if(data.startDate&&data.endDate){
      sqlStr = `SELECT * from uOrder where userName='${data.userName}' and if(${data.status}>0,status=${data.status},status>2) and if(${data.uSchoolId}>0,uSchoolId=${data.uSchoolId},1=1) and orderNum LIKE '%${data.orderNum}%' and time<='${data.endDate}' and time>='${data.startDate}' ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`
    }else{
      sqlStr = `SELECT * from uOrder where userName='${data.userName}' and if(${data.status}>0,status=${data.status},status>2) and if(${data.uSchoolId}>0,uSchoolId=${data.uSchoolId},1=1) and orderNum LIKE '%${data.orderNum}%'  ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`
    }
    return sqlStr
  },
  uOrderManageTotal:(data)=>{
    let sqlStr = ''
    if(data.startDate&&data.endDate){
      sqlStr = `SELECT count(*) from uOrder where userName='${data.userName}' and if(${data.status}>0,status=${data.status},status>2) and if(${data.uSchoolId}>0,uSchoolId=${data.uSchoolId},1=1) and orderNum LIKE '%${data.orderNum}%' and time<='${data.endDate}' and time>='${data.startDate}'`
    }else{
      sqlStr = `SELECT count(*) from uOrder where userName='${data.userName}' and if(${data.status}>0,status=${data.status},status>2) and if(${data.uSchoolId}>0,uSchoolId=${data.uSchoolId},1=1) and orderNum LIKE '%${data.orderNum}%'`
    }
    return sqlStr
  },
  

  // 获取爆款区分页列表
  topAreaList:(data)=>`SELECT * from uTop where uSchoolId=${data.uSchoolId}  ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`,
  topAreaTotal:(data)=>`SELECT count(*) from uTop where uSchoolId=${data.uSchoolId}`,

  // 校园管理员订单列表
  uOrderBusList:(data)=>{
    let sqlStr = ''
    if(data.startDate&&data.endDate){
      sqlStr = `SELECT * from uOrder where uSchoolId=${data.uSchoolId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1)  and if(${data.status}>0,status=${data.status},status>2)  and orderNum LIKE '%${data.orderNum}%' and time<='${data.endDate}' and time>='${data.startDate}' ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`
    }else{
      sqlStr = `SELECT * from uOrder where uSchoolId=${data.uSchoolId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1) and if(${data.status}>0,status=${data.status},status>2)  and orderNum LIKE '%${data.orderNum}%'  ORDER BY id DESC LIMIT ${getPageNum(data.pageNo,data.pageSize)}, ${data.pageSize}`
    }
    console.log(sqlStr)
    return sqlStr
  },
  uOrderBusTotal:(data)=>{
    let sqlStr = ''
    if(data.startDate&&data.endDate){
      sqlStr = `SELECT count(*) from uOrder where uSchoolId=${data.uSchoolId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1) and if(${data.status}>0,status=${data.status},status>2)  and orderNum LIKE '%${data.orderNum}%' and time<='${data.endDate}' and time>='${data.startDate}'`
    }else{
      sqlStr = `SELECT count(*) from uOrder where uSchoolId=${data.uSchoolId} and if(${data.businessManageId}>0,businessManageId=${data.businessManageId},1=1) and if(${data.status}>0,status=${data.status},status>2)  and orderNum LIKE '%${data.orderNum}%'`
    }
    return sqlStr
  },
};


